﻿using CraftParameterConfig.Domain.Entity;
using CraftParameterConfig.Domain.Interfaces;
using CraftParameterConfig.Infrastructure.Common.Enums;
using CraftParameterConfig.Services.Interfaces;
using CraftParameterConfig.Services.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CraftParameterConfig.Services.Data
{
    public class ParameterService : IParameterService
    {
        ITableNameRespository tableNameRespository;
        ITableIndexRespository tableIndexRespository;
        ITableDataRespository tableDataRespository;
        IParameterAccessRespository parameterAccessRespository;
        IParameterIndexRespository parameterIndexRespository;
        IParameterDataRespository parameterDataRespository;
        public ParameterService(ITableNameRespository tableNameRespository, 
            ITableIndexRespository tableIndexRespository,
            ITableDataRespository tableDataRespository, 
            IParameterAccessRespository parameterAccessRespository,
            IParameterIndexRespository parameterIndexRespository, 
            IParameterDataRespository parameterDataRespository)
        {
            this.tableNameRespository = tableNameRespository;
            this.tableIndexRespository = tableIndexRespository;
            this.tableDataRespository = tableDataRespository;
            this.parameterAccessRespository = parameterAccessRespository;
            this.parameterIndexRespository = parameterIndexRespository;
            this.parameterDataRespository = parameterDataRespository;
        }
        public string AddParameter(ParameterAccessDTO parameterAccessDTO, ParameterIndexDTO parameterIndexDTO, ParameterDataDTO parameterDataDTO)
        {
            ParameterAccess parameterAccess = new ParameterAccess(
                Guid.Parse(parameterAccessDTO.MaindID), 
                Convert.ToDateTime(parameterAccessDTO.Validity), 
                parameterAccessDTO.OrderBatch, 
                (FileType)parameterAccessDTO.FileType);
            parameterAccess.CREATE_USER = "SC";

            ParameterIndexValue parameterIndexValue = new ParameterIndexValue(parameterAccess.GuidID,parameterIndexDTO.FieldName, parameterIndexDTO.FieldValue);
            parameterIndexValue.CREATE_USER = "SC";

            ParameterDataValue parameterDataValue = new ParameterDataValue(parameterAccess.GuidID, parameterDataDTO.FieldName, parameterDataDTO.FieldValue);
            parameterDataValue.CREATE_USER = "SC";

            parameterAccessRespository.Add(parameterAccess);
            parameterIndexRespository.Add(parameterIndexValue);
            parameterDataRespository.Add(parameterDataValue);

            return parameterAccess.GuidID.ToString();
        }

        public string AddParameterRange(ParameterAccessDTO parameterAccessDTO, List<ParameterIndexDTO> parameterIndexDTOs, List<ParameterDataDTO> parameterDataDTOs)
        {
            List<ParameterIndexValue> parameterIndexValues = new List<ParameterIndexValue>();
            List<ParameterDataValue> parameterDataValues = new List<ParameterDataValue>();

            ParameterAccess parameterAccess = new ParameterAccess(
               Guid.Parse(parameterAccessDTO.MaindID),
               Convert.ToDateTime(parameterAccessDTO.Validity),
               parameterAccessDTO.OrderBatch,
               (FileType)parameterAccessDTO.FileType);
            parameterAccess.CREATE_USER = "SC";


            foreach (var item in parameterIndexDTOs)
            {
                ParameterIndexValue parameterIndexValue = new ParameterIndexValue(parameterAccess.GuidID, item.FieldName, item.FieldValue);
                parameterIndexValue.CREATE_USER = "SC";
                parameterIndexValues.Add(parameterIndexValue);
            }

            foreach (var item in parameterDataDTOs)
            {
                ParameterDataValue parameterDataValue = new ParameterDataValue(parameterAccess.GuidID, item.FieldName, item.FieldValue);
                parameterDataValue.CREATE_USER = "SC";
                parameterDataValues.Add(parameterDataValue);
            }

            parameterAccessRespository.Add(parameterAccess);
            parameterIndexRespository.AddRange(parameterIndexValues);
            parameterDataRespository.AddRange(parameterDataValues);

            return DateTime.Now.ToString();
        }

        public void DeleteParameter(ParameterAccessDTO parameterAccessDTO)
        {
            parameterAccessRespository.Delete(Guid.Parse(parameterAccessDTO.GuidID));
        }

        public void DeleteParameter(string guidId)
        {
            parameterAccessRespository.Delete(Guid.Parse(guidId));
        }

        public List<string> GetParameterAccess( ParameterAccessDTO parameterAccessDTO)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(parameterAccessDTO.OrderBatch))
            {
                string status = "";
                if (parameterAccessDTO.Status != -1)
                {
                    status = string.Format(" and STATUS={0} ", parameterAccessDTO.Status);
                }
                else
                {
                    status = " and STATUS <99 ";
                }
                sb.Append(string.Format(" and ORDERBATCH like '%{0}%' {1} and MAINID='{2}'", parameterAccessDTO.OrderBatch, status, parameterAccessDTO.MaindID));
            }
            else
            {
                string fileType = "";
                if (parameterAccessDTO.FileType>0)
                {
                    fileType = string.Format(" and FILETYPE={0}", parameterAccessDTO.FileType);
                }
                string status = "";
                if (parameterAccessDTO.Status != -1)
                {
                    status = string.Format(" and STATUS={0} ", parameterAccessDTO.Status);
                }
                else
                {
                    status = " and STATUS <99 ";
                }
                sb.Append(string.Format(" {0} and MAINID='{1}' and to_date(VALIDITY,'yyyy/MM/dd Hh24:mi:ss')>=sysdate {2} order by FILETYPE desc",
                    status, parameterAccessDTO.MaindID, fileType));
            }

            var ps = parameterAccessRespository.GetEntitysByContition(sb.ToString());
            return ps.Select(t => string.Format("'{0}'", t.GuidID.ToString())).ToList();
        }

        public List<ParameterDataDTO> GetParameterDatasById(string guidId)
        {
            string condition = string.Format(" where MAINID='{0}'", guidId);
            var source = parameterDataRespository.GetEntitysByContition(condition);
            List<ParameterDataDTO> parameterDataDTOs = new List<ParameterDataDTO>();
            foreach (var item in source)
            {
                ParameterDataDTO parameterDataDTO = new ParameterDataDTO();
                parameterDataDTO.MainID = item.MainID.ToString();
                parameterDataDTO.FieldName = item.FieldName;
                parameterDataDTO.FieldValue = item.FieldValue;
                parameterDataDTOs.Add(parameterDataDTO);
            }
            return parameterDataDTOs;
        }

        public DataTable GetParameterIndexsByCondition(string guidId,Dictionary<TableIndexDTO, string> keyValuePairs,List<string> indexIds)
        {
            //注意,此方法非常复杂,可以实现查询的功能,但对于千万行记录的表,查询效率可能很低,此方法逻辑请一定在清楚实现方法之后再进行修改
            StringBuilder sb = new StringBuilder();
            sb.Append(" where 1=1");
            foreach (var item in keyValuePairs)
            {
                TableIndexDTO temp = item.Key;
                IndexDataType dataType = (IndexDataType)Enum.Parse(typeof(IndexDataType), temp.DataType);
                DataCondition dataCondition = (DataCondition)Enum.Parse(typeof(DataCondition), temp.DataCondition);

                if (dataType == IndexDataType.Number)
                {
                    string con = GetCondition(dataCondition);
                    if (con == "CA" || con == "EX")
                    {
                        throw new Exception(string.Format("{0}的计算规则有误,请联系管理员确认!", temp.Name));
                    }
                    else if (con == "RG")
                    {
                        if (item.Value.Contains("-"))
                        {
                            string[] ranges = item.Value.Split('-');
                            sb.Append(string.Format(@" and ((to_Number(substr2(t.TEMP, 1, instr(t.temp, '-', 1, 1) - 1)) > {0} and
                                                       to_Number(substr2(t.TEMP, 1, instr(t.temp, '-', 1, 1) - 1)) <= {1}) or
                                                       (to_Number(substr2(t.TEMP, 1, instr(t.temp, '-', 1, 1) - 1)) <= {0} and
                                                       to_Number(substr2(t.temp, instr(t.temp, '-', 1, 1) + 1)) >= {0}))", ranges[0], ranges[1]));

                        }
                        else
                        {
                            sb.Append(string.Format(" and to_Number(substr2({0},1,instr({0},'-',1,1)-1)) DOWN<={1}", temp.Name, item.Value));
                            sb.Append(string.Format(" and to_Number(substr2({0},instr({0},'-',1,1)+1)) UP>{1}", temp.Name, item.Value));
                        }
                    }
                    else
                    {
                        sb.Append(string.Format(" and {0}{1}{2}", temp.Name, con, item.Value));
                    }
                }
                else if (dataType == IndexDataType.DatTime)
                {
                    string con = GetCondition(dataCondition);
                    if (con == "RG" || con == "CA" || con == "EX")
                    {
                        throw new Exception(string.Format("{0}的计算规则有误,请联系管理员确认!", temp.Name));
                    }
                    else
                    {
                        sb.Append(string.Format(" and {0}{1}to_date('{2}','yyyy/MM/dd HH24:mi:ss')", temp.Name, con, item.Value));
                    }
                }
                else
                {
                    string con = GetCondition(dataCondition);
                    if (con == "RG" || con == "EX")
                    {
                        throw new Exception(string.Format("{0}的计算规则有误,请联系管理员确认!", temp.Name));
                    }
                    else if (con == "CA")
                    {
                        sb.Append(string.Format(" and {0} like '%{1}%'", temp.Name, item.Value));
                    }
                    else
                    {
                        sb.Append(string.Format(" and {0}{1}'{2}'", temp.Name, con, item.Value));
                    }
                }
            }
            List<TableIndexDTO> tableIndexs = GetTableIndexs(guidId);
            StringBuilder sbN = new StringBuilder();
            foreach (var item in tableIndexs.OrderBy(t=>t.Order_Index))
            {
                sbN.Append(string.Format("'{0}' as {0},", item.Name));
            }

            string maidIds = string.Join(",", indexIds);
            if (maidIds.Length > 1)
            {
                maidIds = string.Format("and MAINID in ({0})", maidIds);
            }
            else
            {
                maidIds = string.Format("and MAINID in ('{0}')", "-111111");//当没有查询到ParameterAccess的时候,默认不能查询到数据
            }

            string sql = string.Format("with x as  (select * from (select MAINID 编号,FIELDNAME,FIELDVALUE from MDA_PARAMETER_INDEX_VALUE  where 1=1 {0} ) pivot (MAX(FIELDVALUE) for FIELDNAME in ({1})))",
                 maidIds, sbN.ToString().Trim(','));//此处通过行列转换先将数据整理出来
            string sqlResult = string.Format("{0} select * from x {1}", sql, sb.ToString());

            return parameterIndexRespository.GetTable(sqlResult);
        }
        /// <summary>
        /// 判断指定的值是否在范围内
        /// 此方法暂时用不到
        /// </summary>
        /// <param name="range">标准范围值</param>
        /// <param name="source">新范围值或者指定值</param>
        /// <returns></returns>
        private bool IsInRange(string range, string source)
        {
            double rd = 0;
            double ru = 0;

            double sd = 0;
            double su = 0;

            string[] ranges = range.Split('-');
            double.TryParse(ranges[0], out rd);
            double.TryParse(ranges[1], out ru);

            if (rd > ru)
            {
                throw new Exception("指定范围标准下限高于上限,请联系管理员!");
            }
            if (source.Contains("-"))
            {
                string[] sources = source.Split('-');
                double.TryParse(sources[0], out sd);
                double.TryParse(sources[1], out su);
                if (sd > su)
                {
                    throw new Exception("指定范围原始值下限高于上限,请联系管理员!");
                }
                if ((sd >= rd && sd < ru) || (su >= rd && su < ru))
                {
                    return true;
                }
            }
            else
            {
                double.TryParse(source, out sd);
                if (sd >= rd && sd < ru)
                {
                    return true;
                }
            }
            return false;
        }
        private string GetCondition(DataCondition dataCondition)
        {
            switch (dataCondition)
            {
                case DataCondition.Equal:
                    return "=";
                case DataCondition.Great:
                    return ">";
                case DataCondition.Less:
                    return "<";
                case DataCondition.GreatEqual:
                    return ">=";
                case DataCondition.LessEqual:
                    return "<=";
                case DataCondition.NotEqual:
                    return "<>";
                case DataCondition.Contaions:
                    return "CA";
                case DataCondition.Range:
                    return "RG";
                default:
                    return "EX";
            }
        }
        public List<ParameterIndexDTO> GetParameterIndexsById(string guidId)
        {
            string condition = string.Format(" where MAINID='{0}'", guidId);
            var source = parameterIndexRespository.GetEntitysByContition(condition);
            List<ParameterIndexDTO> parameterIndexDTOs = new List<ParameterIndexDTO>();
            foreach (var item in source)
            {
                ParameterIndexDTO parameterIndexDTO = new ParameterIndexDTO();
                parameterIndexDTO.MainID = item.MainID.ToString();
                parameterIndexDTO.FieldName = item.FieldName;
                parameterIndexDTO.FieldValue = item.FieldValue;
                parameterIndexDTOs.Add(parameterIndexDTO);
            }
            return parameterIndexDTOs;
        }

        public List<TableDataDTO> GetTableDatas(string guidId)
        {
            var  indexs= tableDataRespository.GetEntitysByContition(string.Format("where t.MAINID='{0}'", guidId));
            List<TableDataDTO> result = new List<TableDataDTO>();
            foreach (var item in indexs)
            {
                TableDataDTO temp = new TableDataDTO();
                temp.Name = item.Name;
                temp.Order_Index = item.Order_Index;
                temp.NeedBaseValue = item.NeedBaseValue?"TRUE":"FALSE";
                temp.NeedLHValue = item.NeedLHValue ? "TRUE" : "FALSE";
                temp.NeedLLHHValue = item.NeedLLHHValue ? "TRUE" : "FALSE";
                temp.DataType = item.DataType.ToString();
                result.Add(temp);
            }
            return result;
        }

        public List<TableIndexDTO> GetTableIndexs(string guidId)
        {
            var indexs = tableIndexRespository.GetEntitysByContition(string.Format("where t.MAINID='{0}'", guidId));
            List<TableIndexDTO> result = new List<TableIndexDTO>();
            foreach (var item in indexs)
            {
                TableIndexDTO temp = new TableIndexDTO();
                temp.Name = item.Name;
                temp.Order_Index = item.Order_Index;
                temp.DataType = item.DataType.ToString();
                temp.DataCondition = item.DataCondition.ToString();
                result.Add(temp);
            }
            return result;
        }

        public DataTable GetTableName()
        {
            return tableNameRespository.GetTable("");
        }

        public string UpdateData(List<ParameterDataDTO> parameterDataDTOs)
        {
            List<ParameterDataValue> parameterDataValues = new List<ParameterDataValue>();
            foreach (var item in parameterDataDTOs)
            {
                ParameterDataValue parameterDataValue = new ParameterDataValue(Guid.Parse(item.MainID), item.FieldName, item.FieldValue);
                parameterDataValues.Add(parameterDataValue);
            }
            parameterDataRespository.Update(parameterDataValues);
            return DateTime.Now.ToString();
        }

        public ParameterAccessDTO GetParameterAccess(string guidId)
        {
            var pad = parameterAccessRespository.GetEntityByGuid(Guid.Parse(guidId));
            ParameterAccessDTO parameterAccessDTO = new ParameterAccessDTO();
            parameterAccessDTO.FileType = (int)pad.FileType;
            parameterAccessDTO.GuidID = pad.GuidID.ToString();
            parameterAccessDTO.Validity = pad.Validity;
            parameterAccessDTO.OrderBatch = pad.OrderBatch;
            parameterAccessDTO.Status = pad.Status;

            return parameterAccessDTO;
        }

        public string AuditData(List<ParameterAccessDTO> parameterAccessDTOs)
        {
            List<ParameterAccess> parameterAccesses = new List<ParameterAccess>();
            foreach (var item in parameterAccessDTOs)
            {
                ParameterAccess pa = new ParameterAccess();
                pa.GuidID = Guid.Parse(item.GuidID);
                pa.Status = item.Status;
                parameterAccesses.Add(pa);
            }
            parameterAccessRespository.UpdateRange(parameterAccesses);
            return DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
        }

        public string AuditData(ParameterAccessDTO parameterAccessDTO)
        {
            ParameterAccess pa = new ParameterAccess();
            pa.GuidID = Guid.Parse(parameterAccessDTO.GuidID);
            pa.Status = parameterAccessDTO.Status;
            parameterAccessRespository.Update(pa);
            return parameterAccessDTO.GuidID;
        }

        public bool IsExist(string guidId, Dictionary<TableIndexDTO, string> keyValuePairs, List<string> indexIds)
        {
            var result = GetParameterIndexsByCondition(guidId, keyValuePairs, indexIds);
            return result.Rows.Count > 0;
        }

        public string AddParameterRange(List<ImportDataDTO> importDataDTOs)
        {
            List<ParameterAccess> parameterAccesses = new List<ParameterAccess>();
            List<ParameterIndexValue> parameterIndexValues = new List<ParameterIndexValue>();
            List<ParameterDataValue> parameterDataValues = new List<ParameterDataValue>();
            foreach (var item in importDataDTOs)
            {
                ParameterAccess pa = new ParameterAccess(
                   Guid.Parse(item.ParameterAccessDTO.MaindID),
                   Convert.ToDateTime(item.ParameterAccessDTO.Validity),
                   item.ParameterAccessDTO.OrderBatch,
                   (FileType)item.ParameterAccessDTO.FileType);
                pa.CREATE_USER = "SC";
                parameterAccesses.Add(pa);

                foreach (var index in item.ParameterIndexDTOs)
                {
                    ParameterIndexValue parameterIndexValue = new ParameterIndexValue(pa.GuidID, index.FieldName, index.FieldValue);
                    parameterIndexValue.CREATE_USER = "SC";
                    parameterIndexValues.Add(parameterIndexValue);
                }

                foreach (var data in item.ParameterDataDTOs)
                {
                    ParameterDataValue parameterDataValue = new ParameterDataValue(pa.GuidID, data.FieldName, data.FieldValue);
                    parameterDataValue.CREATE_USER = "SC";
                    parameterDataValues.Add(parameterDataValue);
                }
            }
            parameterAccessRespository.AddRange(parameterAccesses);
            parameterIndexRespository.AddRange(parameterIndexValues);
            parameterDataRespository.AddRange(parameterDataValues);

            return DateTime.Now.ToString();
        }
    }
}
